package com.maotaiapp.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class MaotaiAppDao{
	
	public List<Map<String,Object>> getAllIP(Connection c){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		try {
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select id,ip,port from ip where TIMESTAMPDIFF(MINUTE, createtime,now())<8 order by rand() limit 200	";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("id",rs.getString("id"));
				map.put("ip",rs.getString("ip"));
				map.put("port",rs.getString("port"));
				list.add(map);
			}
			stmt.close();
			c.commit();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return list;
	}
	
	public List<Map<String,Object>> getAllMaotaiAppZhanghaoNotYuYue(Connection c,String PID){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		try {
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select code,pwd,userId,addressId,invoiceId,UUID1,UUID2,acwTc,serverId,vshopMember,sessionId,lat,lon "+
				"	from maotaiapp "+
				"	where 1=1 "+
				"	and code in (select code from maotaiappyuyueitem item where item.goodsId='"+PID+"') "+
//				"	and isyuyue='3' "+
				"	and vshopMember!='' "+
				"	and orderstatus='0' "+
				"	order by rand() limit 200	";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("code",rs.getString("code"));
				map.put("pwd",rs.getString("pwd"));
				map.put("userId",rs.getString("userId"));
				map.put("addressId",rs.getString("addressId"));
				map.put("invoiceId",rs.getString("invoiceId"));
				map.put("UUID1",rs.getString("UUID1"));
				map.put("UUID2",rs.getString("UUID2"));
				map.put("acwTc",rs.getString("acwTc"));
				map.put("serverId",rs.getString("serverId"));
				map.put("vshopMember",rs.getString("vshopMember"));
				map.put("sessionId",rs.getString("sessionId"));
				map.put("lat",rs.getString("lat"));
				map.put("lon",rs.getString("lon"));
				list.add(map);
			}
			stmt.close();
			c.commit();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return list;
	}
	public List<Map<String,Object>> getAllMaotaiAppZhanghaoNo(Connection c){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		try {
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select app.code,pwd,userId,addressId,invoiceId,UUID1,UUID2,acwTc,serverId,vshopMember,sessionId,addr.lat,addr.lon "+ 
				" 	from maotaiapp app join maotaiappaddress addr on app.code = addr.code "+
				"	where 1=1 "+
//				"	and isyuyue='3' "+
				"	and userId!='' "+
				"	and vshopMember!='' "+
				"	and orderstatus='0' "+
				"	order by rand() limit 200	";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("code",rs.getString("code"));
				map.put("pwd",rs.getString("pwd"));
				map.put("userId",rs.getString("userId"));
				map.put("addressId",rs.getString("addressId"));
				map.put("invoiceId",rs.getString("invoiceId"));
				map.put("UUID1",rs.getString("UUID1"));
				map.put("UUID2",rs.getString("UUID2"));
				map.put("acwTc",rs.getString("acwTc"));
				map.put("serverId",rs.getString("serverId"));
				map.put("vshopMember",rs.getString("vshopMember"));
				map.put("sessionId",rs.getString("sessionId"));
				map.put("lat",rs.getString("lat"));
				map.put("lon",rs.getString("lon"));
				list.add(map);
			}
			stmt.close();
			c.commit();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return list;
	}
	public List<Map<String,Object>> getAllMaotaiAppZhanghao(Connection c,String PID){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		try {
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select code,pwd,userId,addressId,invoiceId,UUID1,UUID2,acwTc,serverId,vshopMember,sessionId,lat,lon "+
				"	from maotaiapp "+
				"	where 1=1 "+
				"	and code in (select code from maotaiappyuyueitem item where item.goodsId='"+PID+"' and status='3') "+
//				"	and isyuyue='3' "+
				"	and vshopMember!='' "+
				"	and orderstatus='0' "+
				"	order by rand() limit 200	";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("code",rs.getString("code"));
				map.put("pwd",rs.getString("pwd"));
				map.put("userId",rs.getString("userId"));
				map.put("addressId",rs.getString("addressId"));
				map.put("invoiceId",rs.getString("invoiceId"));
				map.put("UUID1",rs.getString("UUID1"));
				map.put("UUID2",rs.getString("UUID2"));
				map.put("acwTc",rs.getString("acwTc"));
				map.put("serverId",rs.getString("serverId"));
				map.put("vshopMember",rs.getString("vshopMember"));
				map.put("sessionId",rs.getString("sessionId"));
				map.put("lat",rs.getString("lat"));
				map.put("lon",rs.getString("lon"));
				list.add(map);
			}
			stmt.close();
			c.commit();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return list;
	}
	public List<Map<String,Object>> getAllMaotaiAppZhanghao(Connection c){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		try {
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select code,pwd,userId,addressId,invoiceId,UUID1,UUID2,acwTc,serverId,vshopMember,sessionId,lat,lon "+
				"	from maotaiapp "+
				"	where 1=1 "+
				"	and code in (select code from maotaiappyuyueitem item where status='3') "+
//				"	and isyuyue='3' "+
				"	and vshopMember!='' "+
				"	and orderstatus='0' "+
				"	order by rand() limit 200 ";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("code",rs.getString("code"));
				map.put("pwd",rs.getString("pwd"));
				map.put("userId",rs.getString("userId"));
				map.put("addressId",rs.getString("addressId"));
				map.put("invoiceId",rs.getString("invoiceId"));
				map.put("UUID1",rs.getString("UUID1"));
				map.put("UUID2",rs.getString("UUID2"));
				map.put("acwTc",rs.getString("acwTc"));
				map.put("serverId",rs.getString("serverId"));
				map.put("vshopMember",rs.getString("vshopMember"));
				map.put("sessionId",rs.getString("sessionId"));
				map.put("lat",rs.getString("lat"));
				map.put("lon",rs.getString("lon"));
				list.add(map);
			}
			stmt.close();
			c.commit();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return list;
	}
	public List<Map<String,Object>> getAllMaotaiSession(Connection c){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		try {
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select alisessionid,alisessionitem from maotaiappsession where TIMESTAMPDIFF(MINUTE, createtime,now())<20 order by rand() limit 200	";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("alisessionid",rs.getString("alisessionid"));
				map.put("alisessionitem",rs.getString("alisessionitem"));
				list.add(map);
			}
			stmt.close();
			c.commit();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return list;
	}
	public Map<String,Object> selectOneSessionID() {
		Connection c = null;
		Statement stmt = null;
		Map<String,Object> map = new HashMap<String,Object>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager.getConnection("jdbc:mysql://localhost:3306/maotai?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			System.out.println("Opened database successfully");
			stmt = c.createStatement();
			String sql = "select alisessionid,alisessionitem from maotaiappsession where 1=1 and TIMESTAMPDIFF(MINUTE, createtime,now())<10 order by RAND() limit 1 ";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				map.put("alisessionid",rs.getString("alisessionid"));
				map.put("alisessionitem",rs.getString("alisessionitem"));
			}
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return map;
	}
	public int deleteSession(Map<String,Object> map) {
		Connection c = null;
		Statement stmt = null;
		int count = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager.getConnection("jdbc:mysql://localhost:3306/maotai?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			System.out.println("Opened database successfully");
			stmt = c.createStatement();
			String sql = "delete from maotaiappsession where alisessionid = '"+map.get("alisessionid")+"' ";
			count = stmt.executeUpdate(sql);
			
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return count;
	}
	public int updateMaotaiAppOrderStatus(Map<String,Object> map) {
		Connection c = null;
		Statement stmt = null;
		int count = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager.getConnection("jdbc:mysql://localhost:3306/maotai?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			System.out.println("Opened database successfully");
			stmt = c.createStatement();
			String sql = "update maotaiapp set orderstatus='1' where code = '"+map.get("code")+"' ";
			count = stmt.executeUpdate(sql);
			String sql2 = "insert into maotaiapporder(orderid,code,pwd,isbuy,buytime) values('"+
			map.get("orderId")+"','"+map.get("code")+"','"+map.get("pwd")+"','1',now())  ";
			stmt.executeUpdate(sql2);
			stmt.close();
			c.commit();
			c.close();
			System.out.println("closed database successfully");
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return count;
	}
}